![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
The subset of commands for adding, removing, and changing the data contained in tables is the Data Maintenance Language (DML). As pointed out earlier, the data is manifest in the form of rows. So, basically, DML performs row-based operations. Lets see how this works by inserting an entry (row) in the EMPLOYEE table:
INSERT INTO EMPLOYEE VALUES ( '00201', 'Pratik', 'Patel', 'Author', '' );
Here we have inserted the appropriate information in the correct order into the EMPLOYEE table. To be safe, you can specify which field each of the listed tokens goes into:
INSERT INTO EMPLOYEE (empno, lastname, firstname, function, department) VALUES ( '00201', 'Pratik', 'Patel', 'Author', '' );
If you dont want to add all the fields in the row, you can specify only the fields you wish to add:
INSERT INTO EMPLOYEE (empno, lastname, firstname, function) VALUES ( '00201', 'Pratik', 'Patel', 'Author' );
As you can see, I chose not to add anything in the department field. Note that if a fields check constraint is not met, or a table check is not met, an error will be produced. For example, if we did not add something under the firstname field, an error would have been returned because we defined the tables firstname column check as NOT NULL. We did not set up a check for the department field, so the previous command would not produce an error.
To delete a tables contents without removing the table completely, you can run a command like this:
DELETE FROM EMPLOYEE;
This statement will wipe the table clean, leaving no data in any of the columns, and, essentially, deleting all of the rows in the table. Deleting a single entry requires that you specify some criteria for deletion:
DELETE FROM EMPLOYEE WHERE empno='00201';
You can delete multiple rows with this type of operation, as well. If the WHERE clause matches more than one row, all of the rows will be deleted. You can also delete multiple entries by using the SELECT command in the WHERE clause; we will get to the SELECT command in the next section.
If you really want to get fancy, you can use one statement to delete the same row from more than one table:
DELETE FROM EMPLOYEE, CONFIDENTIAL WHERE empno='00201';
The final command I want to cover in this section is UPDATE. This command allows you to change one or more existing fields in a row. Here is a simple example of how to change the firstname field in the EMPLOYEE table:
UPDATE EMPLOYEE SET firstname = 'PR' WHERE empno='00201';
We can set more than one field, if we wish, by adding more expressions, separated by commas, like this:
UPDATE EMPLOYEE SET firstname='PR', function='Writer' WHERE empno='00201';
As youll see in the next section, the WHERE clause can take the form of a SELECT query so that you can change multiple rows according to certain criteria.
You have seen how to create your tables and add data to them, now lets see how to retrieve data from them. The SQL commands that you use to retrieve data from a table are part of the Data Query Language (DQL). DQLs primary command is SELECT, but there are a host of predicates you can use to enhance SELECTs flexibility and specificity. Oftentimes, the key to understanding the process of querying is to think in terms of mathematical sets. SQL, like all fourth-generation languages, is designed to pose the question, What do I want? as opposed to other computer languages, like Java and C++, which pose the question, How do I do it?
Lets look at a set representation of our example database as shown in Figure 2.3. When making queries, youll want to ask these questions:
Previous | Table of Contents | Next |